package database

import (
	"fmt"
	"log"
	"path/filepath"

	"github.com/babytap/server/internal/models"
	"gorm.io/driver/sqlite"
	"gorm.io/gorm"
	"gorm.io/gorm/logger"
)

// CardRepository 卡片数据仓库
type CardRepository struct {
	db      *gorm.DB
	dataDir string
}

// NewCardRepository 创建新的卡片仓库实例
func NewCardRepository(dataDir string) (*CardRepository, error) {
	dbPath := filepath.Join(dataDir, "cards.db")

	// 配置 GORM
	config := &gorm.Config{
		Logger: logger.Default.LogMode(logger.Info), // 开发环境显示 SQL 日志
	}

	// 连接数据库
	db, err := gorm.Open(sqlite.Open(dbPath), config)
	if err != nil {
		return nil, fmt.Errorf("连接数据库失败: %w", err)
	}

	// 先尝试AutoMigrate，如果失败则说明需要手动迁移
	if err := db.AutoMigrate(&models.Card{}); err != nil {
		log.Printf("AutoMigrate失败，尝试手动迁移: %v", err)

		// 检查表结构
		var hasUUIDColumn, hasAudioPathColumn, hasAudioTextColumn bool
		rows, err := db.Raw("PRAGMA table_info(cards)").Rows()
		if err == nil {
			defer rows.Close()
			for rows.Next() {
				var cid, notnull, pk int
				var name, typ string
				var dflt_value interface{}
				rows.Scan(&cid, &name, &typ, &notnull, &dflt_value, &pk)
				switch name {
				case "uuid":
					hasUUIDColumn = true
				case "audio_path":
					hasAudioPathColumn = true
				case "audio_text":
					hasAudioTextColumn = true
				}
			}
		}

		// 如果没有UUID列，需要手动迁移
		if !hasUUIDColumn {
			log.Println("检测到需要添加UUID字段，开始迁移...")

			// 先添加普通列
			if err := db.Exec("ALTER TABLE cards ADD COLUMN uuid TEXT").Error; err != nil {
				return nil, fmt.Errorf("添加UUID列失败: %w", err)
			}

			// 为现有数据生成UUID
			if err := db.Exec(`UPDATE cards SET uuid = 'uuid-' || id || '-' || (strftime('%s', 'now') * 1000 + (id * 1000)) WHERE uuid IS NULL`).Error; err != nil {
				return nil, fmt.Errorf("生成UUID失败: %w", err)
			}

			// 设置NOT NULL约束
			if err := db.Exec("UPDATE cards SET uuid = 'uuid-' || id || '-' || (strftime('%s', 'now') * 1000 + (id * 1000)) WHERE uuid IS NULL OR uuid = ''").Error; err != nil {
				return nil, fmt.Errorf("设置UUID默认值失败: %w", err)
			}

			log.Println("UUID字段迁移完成")
		}

		// 如果没有audio_path列，需要手动迁移
		if !hasAudioPathColumn {
			log.Println("检测到需要添加audio_path字段，开始迁移...")
			if err := db.Exec("ALTER TABLE cards ADD COLUMN audio_path TEXT DEFAULT ''").Error; err != nil {
				return nil, fmt.Errorf("添加audio_path列失败: %w", err)
			}
			log.Println("audio_path字段迁移完成")
		}

		// 如果没有audio_text列，需要手动迁移
		if !hasAudioTextColumn {
			log.Println("检测到需要添加audio_text字段，开始迁移...")
			if err := db.Exec("ALTER TABLE cards ADD COLUMN audio_text TEXT DEFAULT ''").Error; err != nil {
				return nil, fmt.Errorf("添加audio_text列失败: %w", err)
			}
			log.Println("audio_text字段迁移完成")
		}
	} else {
		log.Println("AutoMigrate成功，表结构已更新")
	}

	// 验证表结构
	log.Println("🔍 验证数据库表结构...")
	rows, err := db.Raw("PRAGMA table_info(cards)").Rows()
	if err == nil {
		defer rows.Close()
		log.Println("📋 数据库表结构:")
		for rows.Next() {
			var cid, notnull, pk int
			var name, typ string
			var dflt_value interface{}
			rows.Scan(&cid, &name, &typ, &notnull, &dflt_value, &pk)
			log.Printf("  - %s (%s) notnull=%d pk=%d", name, typ, notnull, pk)
		}
	}

	return &CardRepository{db: db, dataDir: dataDir}, nil
}

// GetDataDir 获取数据目录
func (r *CardRepository) GetDataDir() string {
	return r.dataDir
}

// GetAllCards 获取所有卡片
func (r *CardRepository) GetAllCards() ([]*models.Card, error) {
	var cards []*models.Card

	result := r.db.Order("id desc").Find(&cards)
	if result.Error != nil {
		return nil, fmt.Errorf("查询卡片失败: %w", result.Error)
	}

	// 添加调试日志
	log.Printf("📊 查询到 %d 张卡片", len(cards))
	for i, card := range cards {
		log.Printf("📋 卡片 %d: ID=%d, Title=%s, AudioPath=%s, AudioText=%s",
			i+1, card.ID, card.Title, card.AudioPath, card.AudioText)
	}

	return cards, nil
}

// GetCardByID 根据ID获取卡片
func (r *CardRepository) GetCardByID(id int) (*models.Card, error) {
	var card models.Card

	result := r.db.First(&card, id)
	if result.Error != nil {
		if result.Error == gorm.ErrRecordNotFound {
			return nil, nil
		}
		return nil, fmt.Errorf("查询卡片失败: %w", result.Error)
	}

	return &card, nil
}

// CreateCard 创建新卡片
func (r *CardRepository) CreateCard(req *models.CreateCardRequest) (*models.Card, error) {
	card := models.NewCard(req.Title, req.FrontImage, req.BackText)

	// 设置音频信息
	card.AudioPath = req.AudioPath
	card.AudioText = req.AudioText

	result := r.db.Create(card)
	if result.Error != nil {
		return nil, fmt.Errorf("创建卡片失败: %w", result.Error)
	}

	log.Printf("创建卡片成功，ID: %d, UUID: %s, AudioPath: %s, AudioText: %s",
		card.ID, card.UUID, card.AudioPath, card.AudioText)
	return card, nil
}

// UpdateCard 更新卡片
func (r *CardRepository) UpdateCard(id int, req *models.UpdateCardRequest) (*models.Card, error) {
	// 先查询现有卡片
	var card models.Card
	result := r.db.First(&card, id)
	if result.Error != nil {
		if result.Error == gorm.ErrRecordNotFound {
			return nil, nil // 卡片不存在
		}
		return nil, fmt.Errorf("查询现有卡片失败: %w", result.Error)
	}

	// 更新卡片信息（包含音频字段）
	card.UpdateWithAudio(req.Title, req.FrontImage, req.BackText, req.AudioPath, req.AudioText)

	// 保存更新
	result = r.db.Save(&card)
	if result.Error != nil {
		return nil, fmt.Errorf("更新卡片失败: %w", result.Error)
	}

	log.Printf("更新卡片成功，ID: %d", card.ID)
	return &card, nil
}

// DeleteCard 删除卡片
func (r *CardRepository) DeleteCard(id int) (bool, error) {
	result := r.db.Delete(&models.Card{}, id)
	if result.Error != nil {
		return false, fmt.Errorf("删除卡片失败: %w", result.Error)
	}

	success := result.RowsAffected > 0
	if success {
		log.Printf("删除卡片成功，ID: %d", id)
	} else {
		log.Printf("删除卡片失败，ID: %d (可能不存在)", id)
	}

	return success, nil
}

// GetCardCount 获取卡片数量
func (r *CardRepository) GetCardCount() (int, error) {
	var count int64

	result := r.db.Model(&models.Card{}).Count(&count)
	if result.Error != nil {
		return 0, fmt.Errorf("查询卡片数量失败: %w", result.Error)
	}

	return int(count), nil
}

// BatchCreateCards 批量创建卡片
func (r *CardRepository) BatchCreateCards(cards []*models.Card) error {
	result := r.db.CreateInBatches(cards, 100) // 每批100条
	if result.Error != nil {
		return fmt.Errorf("批量创建卡片失败: %w", result.Error)
	}

	log.Printf("批量创建 %d 张卡片成功", len(cards))
	return nil
}

// GetCardsByTitle 根据标题搜索卡片
func (r *CardRepository) GetCardsByTitle(title string) ([]*models.Card, error) {
	var cards []*models.Card

	result := r.db.Where("title LIKE ?", "%"+title+"%").Find(&cards)
	if result.Error != nil {
		return nil, fmt.Errorf("搜索卡片失败: %w", result.Error)
	}

	return cards, nil
}

// GetCardsByDateRange 根据日期范围获取卡片
func (r *CardRepository) GetCardsByDateRange(startDate, endDate string) ([]*models.Card, error) {
	var cards []*models.Card

	result := r.db.Where("created_at BETWEEN ? AND ?", startDate, endDate).Find(&cards)
	if result.Error != nil {
		return nil, fmt.Errorf("查询日期范围卡片失败: %w", result.Error)
	}

	return cards, nil
}

// Close 关闭数据库连接
func (r *CardRepository) Close() error {
	sqlDB, err := r.db.DB()
	if err != nil {
		return fmt.Errorf("获取底层数据库连接失败: %w", err)
	}

	return sqlDB.Close()
}
